package com.atguigu.utils;

import java.io.BufferedInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * 使用preparedStatment批量操作数据
 * update、delete本身就有批量操作的效果
 * 本Demo是要是测试批量插入。使用PreparedStatment实现更高效的批量操作。
 *
 * @author hjwStrart
 * @time 2022-03-07 20:37
 */
public class BathTest {
    public static void main(String[] args) {

        test3();
    }

    //    方式一，使用Statement或PrepareStatement循环插入
    public static void test1() {
        String sql = "insert into person values(null,?); ";
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=UTC", "root", "hjw123..");
            Long startTime = System.currentTimeMillis();
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 2000; i++) {
//     void setBlob(int parameterIndex, InputStream inputStream)
                ps.setString(1, "name_" + i);
                ps.executeUpdate();
            }
            Long endTime = System.currentTimeMillis();
            System.out.println("花费时间：" + (endTime - startTime));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //    方法二：使用addBatch()、executeBatch()、clearBatch()方法
//    mysql是默认关闭批处理操作的，需要在连接数据库是，开启批处理操作。
//    在url后面添加 ? rewriteBatchedStatements = true
    public static void test2() {
        String sql = "insert into person values(null,?); ";
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=UTC", "root", "hjw123..");
            Long startTime = System.currentTimeMillis();
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 2000; i++) {
                ps.setString(1, "name_" + i);
//     void setBlob(int parameterIndex, InputStream inputStream)
//                1."攒"sql
                ps.addBatch();
                if (i % 500 == 0) {
//                    2.执行sql
                    ps.executeBatch();
//                    3.清空batch
                    ps.clearBatch();
                }
            }
            Long endTime = System.currentTimeMillis();
            System.out.println("花费时间：" + (endTime - startTime));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //  方法三：设置MySQL的事务不自动提交
    public static void test3(){
        String sql = "insert into person values(null,?); ";
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=UTC", "root", "hjw123..");
//           关闭事务自动提交
            conn.setAutoCommit(false);
            Long startTime = System.currentTimeMillis();
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 20000; i++) {
                ps.setString(1, "name_" + i);
//     void setBlob(int parameterIndex, InputStream inputStream)
//                1."攒"sql
                ps.addBatch();
                if (i % 500 == 0) {
//                    2.执行sql
                    ps.executeBatch();
//                    3.清空batch
                    ps.clearBatch();
                }
            }
//            提交事务
            conn.commit();
            Long endTime = System.currentTimeMillis();
            System.out.println("花费时间：" + (endTime - startTime));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
